
USE [youhp]
GO
/****** Object:  Table [dbo].[NewsComment]    Script Date: 03/22/2009 19:59:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NewsComment](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[NewsID] [int] NOT NULL,
	[UserID] [int] NULL,
	[Alias] [nvarchar](20) NULL,
	[CommentData] [nvarchar](500) NOT NULL,
	[InsertDateTime] [datetime] NULL,
 CONSTRAINT [PK_NewsComment] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


--region [dbo].[AddNewsComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddNewsComment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddNewsComment]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddNewsComment]
GO

CREATE PROCEDURE [dbo].[AddNewsComment]
	@NewsID int,
	@UserID int,
	@Alias nvarchar(20),
	@CommentData nvarchar(500),
	@InsertDateTime datetime
AS

INSERT INTO [NewsComment] (
	[NewsID],
	[UserID],
	[Alias],
	[CommentData],
	[InsertDateTime]
) VALUES (
	@NewsID,
	@UserID,
	@Alias,
	@CommentData,
	@InsertDateTime 
)

GO
--endregion


--region [dbo].[UpdateNewsComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateNewsComment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateNewsComment]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateNewsComment]
GO

CREATE PROCEDURE [dbo].[UpdateNewsComment]
	@ID int,
	@NewsID int,
	@UserID int,
	@Alias nvarchar(20),
	@CommentData nvarchar(500),
	@InsertDateTime datetime
AS

UPDATE [NewsComment] SET
	[NewsID] = @NewsID,
	[UserID] = @UserID,
	[Alias] = @Alias,
	[CommentData] = @CommentData,
	[InsertDateTime] = @InsertDateTime
WHERE
	[ID] = @ID

GO
--endregion


--region [dbo].[DelNewsComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelNewsComment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelNewsComment]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelNewsComment]
GO

CREATE PROCEDURE [dbo].[DelNewsComment]
	@ID int
AS

DELETE
FROM
	[NewsComment]
WHERE
	[ID] = @ID
--endregion
--region [dbo].[GetNewsComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetNewsCommentCount]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetNewsCommentCount]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetNewsCommentCount]
GO

CREATE PROCEDURE [dbo].[GetNewsCommentCount]
	@NewsID int
AS

SELECT
	count(*)
FROM
	[NewsComment]
WHERE
	[NewsID] = @NewsID

GO
--endregion


--region [dbo].[GetNewsComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetNewsComment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetNewsComment]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetNewsComment]
GO

CREATE PROCEDURE [dbo].[GetNewsComment]
	@ID int
AS

SELECT
	[ID],
	[NewsID],
	[UserID],
	[Alias],
	[CommentData],
	[InsertDateTime]
FROM
	[NewsComment]
WHERE
	[ID] = @ID

GO
--endregion

--region [dbo].[GetNewsComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetNewsComment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetNewsCommentList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetNewsCommentList]
GO

CREATE PROCEDURE [dbo].[GetNewsCommentList]
@NewsID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[NewsID],
	[UserID],
	[Alias],
	[CommentData],
	[InsertDateTime]
FROM
	[NewsComment]

WHERE
	[NewsID] = @NewsID
GO
--endregion


--region [dbo].[GetNewsCommentList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetNewsCommentList]
------------------------------------------------------------------------------------------------------------------------

--IF OBJECT_ID(N'[dbo].[GetNewsCommentList]') IS NOT NULL
--	DROP PROCEDURE [dbo].[GetNewsCommentList]
--GO
--
--CREATE PROCEDURE [dbo].[GetNewsCommentList]
--	@PageSize int,
--	@PageIndex int,
--	@WhereCondition nvarchar(512),
--	@OrderByExpression nvarchar(250) = NULL
--AS
--
--SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--
--DECLARE @SQL nvarchar(3250)
--
--SET @SQL = '
--SELECT 
--	COUNT(Id) 
--FROM [NewsComment]
--	'
--IF @WhereCondition <> ''
--BEGIN
--	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
--END 
--
--EXEC sp_executesql @SQL
--
--SET @SQL = '
--SELECT TOP ' + STR(@pagesize) + '
--	[ID],
--	[NewsID],
--	[UserID],
--	[Alias],
--	[CommentData],
--	[InsertDateTime]
--FROM
--	[NewsComment]
--WHERE
--	[NewsComment].[PostUserId] = [UserInfo].[Id]
--	'
--
--IF @PageIndex > 1
--BEGIN
--SET @SQL = @SQL + '
--	AND [NewsComment].[Id] < 
--	( 
--	SELECT MIN([Id])  
--	FROM ( 
--	SELECT TOP 
--		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
--	FROM [NewsComment] ' 
--
--	IF @WhereCondition <> ''
--	BEGIN
--		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
--	END
--
--	IF @OrderByExpression <> ''
--	BEGIN
--		SET @SQL = @SQL + '
--	ORDER BY
--		' + @OrderByExpression + ') AS tblTmp ) '
--	END
--	ELSE
--	BEGIN
--		SET @SQL = @SQL + ') AS tblTmp ) '
--	END
--
--	IF @WhereCondition <> ''
--	BEGIN
--	SET @SQL = @SQL + ' AND ' + @WhereCondition
--	END
--	
--	IF @OrderByExpression <> ''
--	BEGIN
--		SET @SQL = @SQL + '
--	ORDER BY
--		' + @OrderByExpression 
--	END	
--END
--ELSE 
--BEGIN
--	IF @WhereCondition <> ''
--	BEGIN
--	SET @SQL = @SQL + ' AND ' + @WhereCondition
--	END
--	
--	IF @OrderByExpression <> ''
--	BEGIN
--		SET @SQL = @SQL + ' 
--	ORDER BY
--		' + @OrderByExpression 
--	END	
--END
--
--EXEC sp_executesql @SQL
--
--GO
--endregion


